<?php
class archive{
       protected static $_instance; 
    
private function __construct() {
    
    /*SELECT DATE_FORMAT(`post_date`, '%Y-%m-%d') AS field FROM `posts` GROUP by field


        SELECT p.type, count(p.ID) AS countid 
        FROM `posts` AS p
        LEFT JOIN `relations` AS r ON p.ID = r.post_id
                                LEFT JOIN `category` AS c ON r.cat_id = c.ID
        WHERE DATE_FORMAT(`post_date`, '%Y')='2012'
        GROUP BY p.type


            SELECT c.cat_name, count(p.ID) AS countid 
            FROM `posts` AS p
            LEFT JOIN `relations` AS r ON p.ID = r.post_id
                                    LEFT JOIN `category` AS c ON r.cat_id = c.ID
            WHERE DATE_FORMAT(`post_date`, '%Y')='2012' AND p.type='Articles'
            GROUP BY c.ID


     */
    $this->putScript();
  
    
    $this->getArchive();
    
}
private function getYear(){
    $sql="SELECT DATE_FORMAT(`post_date`, '%Y') AS date FROM `posts` GROUP by date";
    $result = mysql_query($sql) or die('Error, insert query failed'.  mysql_error());

        while ($row = mysql_fetch_assoc($result)) { 
       
            echo '<li>'.'<span class="folder">'. $row['date'].'</span>'.
                    '<ul>';
                   $this->getData($row['date']);
            echo   '</ul></li>';
        }
}    
private function getData($year){
    $sql="SELECT DATE_FORMAT(`post_date`, '%Y-%M') AS date FROM `posts` 
            WHERE DATE_FORMAT(`post_date`, '%Y')='$year'
            GROUP by date";
    $result = mysql_query($sql) or die('Error, insert query failed'.  mysql_error());
        while ($row = mysql_fetch_assoc($result)) { 
            $month = explode("-", $row['date']);
            echo '<li>'.'<span class="folder">'.$month[1].'</span>'.
                    '<ul>';
               // echo $month[1]."<br>";
                        $this->getType($row['date']);
            echo   '</ul></li>';
            
        }
            
}
private function getType($date){
    
    $sql="SELECT p.type, count(p.ID) AS countid 
        FROM `posts` AS p
        LEFT JOIN `relations` AS r ON p.ID = r.post_id
                                LEFT JOIN `category` AS c ON r.cat_id = c.ID
        WHERE DATE_FORMAT(`post_date`, '%Y-%M')='$date'
        GROUP BY p.type";
    
    $result = mysql_query($sql) or die('Error, insert query failed'.  mysql_error());
        while ($row = mysql_fetch_assoc($result)) { 
             $row['type']==""?$row['type']='no type':$row['type']; 
           echo  $row['countid']." ".$row['type']." added <br>";
        
                        $this->getCat($date, $row['type']);
            
        }
    
}
private function getCat($date, $cat){
    
    $sql="SELECT c.cat_name, count(p.ID) AS countid 
            FROM `posts` AS p
            LEFT JOIN `relations` AS r ON p.ID = r.post_id
                                    LEFT JOIN `category` AS c ON r.cat_id = c.ID
            WHERE DATE_FORMAT(`post_date`, '%Y-%M')='$date' AND p.type='$cat'
            GROUP BY c.ID";
    
    $result = mysql_query($sql) or die('Error, insert query failed'.  mysql_error());
        while ($row = mysql_fetch_assoc($result)) { 
          echo  "(".$row['countid']." topic ".$row['cat_name'].")<br>";
        }
    
}

private function getArchive(){
    ?>

<div class="lazur"><h4>Archive:</h4>
  <strong>  
       <ul id="navigation">  
            <?php $this->getYear() ?>
       </ul>    
  </strong>
 </div>
<?php
}








private function putScript(){
   ?>

        <script src="../js/jquery.treeview.js" type="text/javascript"></script>
        <script type="text/javascript" src="../js/mytree.js"></script>
        
 
<?php    
}



public static function getInstance(){
            
            if (null===self::$_instance){
                self::$_instance = new self();
            }
            
            return self::$_instance;
        }
        
      
}
?>
